package com.xmc.dao;

import com.xmc.pojo.Course;
import com.xmc.pojo.CourseMsg;
import com.xmc.utils.DBManager;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CourseDao {

    DBManager dbManager = new DBManager();


    public boolean createCourse(Course course){
        StringBuilder sql = new StringBuilder("insert into course values(");
        sql.append("null,"+
                "'"+ course.getCourseid()+"',"+
                "'"+ course.getStudentid()+"',"+
                "'"+ course.getTeachername()+"',"+
                "'"+ course.getCoursename()+"',"+
                "'"+course.getCoursetype()+"',"+
                "'"+course.getCoursexf()+"',"+
                "-1,-1,-1)"
        );

        System.out.println("insert sql:"+sql.toString());
        boolean update = false;
        try {
            update = dbManager.executeUpdate(sql.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return update;
    }


    public boolean createCourseMsg(CourseMsg courseMsg){
        StringBuilder sql = new StringBuilder("insert into course_msg values(");
        sql.append("null,"+
                "'"+ courseMsg.getCourseid()+"',"+
                "'"+ courseMsg.getStudentid()+"',"+
                "'"+ courseMsg.getTeachername()+"',"+
                "'"+ courseMsg.getCoursename()+"',"+
                "'"+ courseMsg.getClassplace()+"',"+
                "'"+ courseMsg.getType()+"',"+
                "'"+ courseMsg.getChapter()+"',"+
                "'"+ courseMsg.getHasCourseTime()+"',"+
                "'"+ courseMsg.getClearTime()+"',"+
                "'"+ courseMsg.getZhouci()+ "')"
        );
        System.out.println("insert sql:"+sql.toString());
        boolean update = false;
        try {
            update = dbManager.executeUpdate(sql.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return update;
    }


    public boolean deleteCourseMsg(String stuCode){
        StringBuilder sql = new StringBuilder(" delete from course_msg where studentid = '" +stuCode+"'" );
        System.out.println("insert sql:"+sql.toString());
        boolean update = false;
        try {
            update = dbManager.executeUpdate(sql.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return update;
    }

    public boolean deleteCourse(String stuCode){
        StringBuilder sql = new StringBuilder(" delete from course where studentid = '" +stuCode+"'" );
        System.out.println("insert sql:"+sql.toString());
        boolean update = false;
        try {
            update = dbManager.executeUpdate(sql.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return update;
    }


    public boolean checkCourseInit(String studentid){
        boolean res = false;
        StringBuilder sql = new StringBuilder("select count(*) from course_msg where studentid = "
                +studentid);
        System.out.println("select : "+ sql);
        ResultSet set = null;
        try {
            set = dbManager.executeQuery(sql.toString());
            set.next();
            if(set.getInt(1)>0){
                res = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return res;
    }

    public List<CourseMsg> getAllCourse(String zhouci,String studentid){
        List<CourseMsg> res = new ArrayList<>();
        StringBuilder sql = new StringBuilder("select * FROM `course_msg` WHERE zhouci = '" +zhouci+"'"
                + "and studentid = "+studentid+
                " ORDER BY clearTime,`type`");
        ResultSet set;
        System.out.println("select : "+ sql);
        try {
            set= dbManager.executeQuery(sql.toString());
            while(set.next()){
                CourseMsg courseMsg = new CourseMsg(
                        set.getInt("id"),
                        set.getString("courseid"),
                        set.getString("studentid"),
                        set.getString("teachername"),
                        set.getString("coursename"),
                        set.getString("classplace"),
                        set.getString("type"),
                        set.getString("chapter"),
                        set.getString("hasCourseTime"),
                        set.getString("clearTime"),
                        set.getString("zhouci")
                );
                res.add(courseMsg);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return res;
    }

    public List<Course> getMyGrade(String studentid,String coursename,String teachername) {

        List<Course> res = new ArrayList<>();

        StringBuilder sql = new StringBuilder("select * FROM `course` WHERE 1=1");
        if (studentid != null){
            sql.append(" and studentid = '"+studentid+"'");
        }
        if (coursename != null){
            sql.append(" and coursename = '"+coursename+"'");
        }
        if (teachername != null){
            sql.append(" and teachername = '"+teachername+"'");
        }
        sql.append(" order by `coursename`");
        ResultSet set;
        System.out.println("select : "+ sql);
        try {
            set= dbManager.executeQuery(sql.toString());
            while(set.next()){
                Course course = new Course(
                        set.getInt("id"),
                        set.getString("courseid"),
                        set.getString("studentid"),
                        set.getString("teachername"),
                        set.getString("coursename"),
                        set.getString("coursetype"),
                        set.getString("coursexf"),
                        set.getFloat("usualgrades"),
                        set.getFloat("examgrade"),
                        set.getInt("totalgrade"));
                res.add(course);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return res;
    }
}
